Release 10.1A: OpenEdge Data Management:
DataServer for ORACLE

Sample Queries

This appendix contains sample queries and the information that the DataServer provides when you specify the QUERY-TUNING(DEBUG SQL) option. In each case, notes explain the DataServer and cursor behavior. The numbers in angle brackets (<n>) indicate cursor handles for SQL statements.

Query 1 

FIND customer 2.
DISPLAY name cust-num postal-code.

FIND NEXT customer.
DISPLAY name cust-num postal-code. 


 OCI call OCIStmtPrepare <2>     sqlcrc = 41633 
   SELECT /*+ INDEX(T0 CUSTOMER##PROGRESS_RECID) */ * FROM  
      DOCTEST.CUSTOMER T0 WHERE PROGRESS_RECID = :rid 
 OCI call OCIStmtExecute-DescribeOnly <2> 
 OCI call omru   <2> 
 OCI call OCIHandleAlloc <0> 
 OCI call OCIStmtPrepare <3>     sqlcrc = 53596 
   SELECT /*+ INDEX_ASC(T0 CUSTOMER##CUST_NUM) */ PROGRESS_RECID  
      FROM DOCTEST.CUSTOMER T0 WHERE ((CUST_NUM = :1))  order by CUST_NUM  
 OCI call OCIStmtExecute <3> 
 OCI call omru   <2> 
 OCI call OCIStmtExecute <2> 
 OCI call OCIHandleAlloc <0> 
 OCI call omru   <3> 
 OCI call OCIStmtPrepare <4>     sqlcrc = 3562 
    SELECT /*+ INDEX_ASC(T0 CUSTOMER##CUST_NUM) */ PROGRESS_RECID  
      FROM DOCTEST.CUSTOMER T0 WHERE ((CUST_NUM > :p1)) order by CUST_NUM  
 OCI call OCIStmtExecute <4> 
 OCI call omru   <2> 
 OCI call OCIStmtExecute <2> 
 OCI call omru   <4> 
  
      Cursor <4> Rows processed 20 (last execution) 
 OCI call OCIHandleFree <0> 
  
      Cursor <2> Rows processed 1 (last execution) 
      Total Rows processed     1 (previous executions) 
 OCI call OCIHandleFree <0> 
  
      Cursor <3> Rows processed 1 (last execution) 
 OCI call OCIHandleFree <0>

<2>    The DataServer uses the cursor to compare schema information and fetch column values.

<3,4>    The WHERE clause generated by the DataServer positions the cursor after the row retrieved by the first use of cursor <2> to retrieve CUSTOMER 2.

Query 2 

FIND customer 2.
DISPLAY name cust-num postal-code.
FIND NEXT customer USE-INDEX Country-Post.

DISPLAY name cust-num postal-code WITH FRAME b. 


 OCI call OCIStmtPrepare <2>     sqlcrc = 41633 
     SELECT /*+ INDEX(T0 CUSTOMER##PROGRESS_RECID) */ * FROM  
      DOCTEST.CUSTOMER T0 WHERE PROGRESS_RECID = :rid 
 OCI call OCIStmtExecute-DescribeOnly <2> 
 OCI call omru   <2> 
 OCI call OCIHandleAlloc <0> 
 OCI call OCIStmtPrepare <3>     sqlcrc = 53596 
     SELECT /*+ INDEX_ASC(T0 CUSTOMER##CUST_NUM) */ PROGRESS_RECID  
      FROM DOCTEST.CUSTOMER T0 WHERE ((CUST_NUM = :1))  order by CUST_NUM  
 OCI call OCIStmtExecute <3> 
 OCI call omru   <2> 
 OCI call OCIStmtExecute <2> 
 OCI call omru   <3> 
 OCI call OCIHandleAlloc <0> 
 OCI call OCIHandleAlloc <0> 
 OCI call omru   <4> 
 OCI call OCIStmtPrepare <5>     sqlcrc = 49589 
     SELECT /*+ INDEX_ASC(T0 CUSTOMER##COUNTRY_POST) */ PROGRESS_RECID 
      FROM DOCTEST.CUSTOMER T0 WHERE ((upper(COUNTRY) = upper(:p1) AND 
      upper(POSTAL_CODE) = upper(:p2) AND PROGRESS_RECID > :p0) OR  
      (upper(COUNTRY) = upper(:p1) AND upper(POSTAL_CODE) > upper(:p2)) OR 
      (upper(COUNTRY) > upper(:p1))) order by upper(COUNTRY),  
      upper(POSTAL_CODE), PROGRESS_RECID  
 OCI call OCIStmtExecute <5> 
 OCI call omru   <2> 
 OCI call OCIStmtExecute <2> 
 OCI call omru   <5> 
  
      Cursor <5> Rows processed 20 (last execution) 
 OCI call OCIHandleFree <0> 
  
      Cursor <2> Rows processed 1 (last execution) 
      Total Rows processed     1 (previous executions) 
 OCI call OCIHandleFree <0> 
  
      Cursor <4> Rows processed 0 (last execution) 
 OCI call OCIHandleFree <0> 
  
      Cursor <3> Rows processed 1 (last execution) 
 OCI call OCIHandleFree <0>

<2>    The DataServer uses the cursor to compare schema information and fetch column values.

<2>    The DataServer uses this cursor for customer 2.

<5>    The WHERE clause generated by the DataServer positions the cursor for country-post after CUSTOMER 2. The ORDER BY clause uses the PROGRESS_RECID column as the final component to guarantee unique ordering.

Query 3 

FIND customer 2.
DISPLAY name cust-num postal-code. 


 OCI call OCIStmtPrepare <2>     sqlcrc = 41633 
     SELECT /*+ INDEX(T0 CUSTOMER##PROGRESS_RECID) */ *  
      FROM DOCTEST.CUSTOMER T0 WHERE PROGRESS_RECID = :rid 
 OCI call OCIStmtExecute-DescribeOnly <2> 
 OCI call omru   <2> 
 OCI call OCIHandleAlloc <0> 
 OCI call OCIStmtPrepare <3>     sqlcrc = 53596 
     SELECT /*+ INDEX_ASC(T0 CUSTOMER##CUST_NUM) */ PROGRESS_RECID  
      FROM DOCTEST.CUSTOMER T0 WHERE ((CUST_NUM = :1))  order by CUST_NUM  
 OCI call OCIStmtExecute <3> 
 OCI call omru   <2> 
 OCI call OCIStmtExecute <2> 
 OCI call omru   <3> 
  
      Cursor <3> Rows processed 1 (last execution) 
 OCI call OCIHandleFree <0> 
  
      Cursor <2> Rows processed 1 (last execution) 
 OCI call OCIHandleFree <0>

<2>    The DataServer uses the cursor to compare schema information and fetch column values.

<3>    This cursor selects the PROGRESS_RECID column for a particular row by CUST_NUM.

Query 4 

FIND FIRST customer.
DISPLAY name cust-num postal-code.
FIND customer 2.
DISPLAY name cust-num postal-code. 


 OCI call OCIStmtPrepare <2>     sqlcrc = 41633 
     SELECT /*+ INDEX(T0 CUSTOMER##PROGRESS_RECID) */ * FROM  
      DOCTEST.CUSTOMER T0 WHERE PROGRESS_RECID = :rid 
 OCI call OCIStmtExecute-DescribeOnly <2> 
 OCI call omru   <2> 
 OCI call OCIHandleAlloc <0> 
 OCI call OCIStmtPrepare <3>     sqlcrc = 1858 
     SELECT /*+ INDEX_ASC(T0 CUSTOMER##CUST_NUM) */ PROGRESS_RECID  
      FROM DOCTEST.CUSTOMER T0 order by CUST_NUM  
 OCI call OCIStmtExecute <3> 
 OCI call omru   <2> 
 OCI call OCIStmtExecute <2> 
 OCI call omru   <3> 
 OCI call OCIHandleAlloc <0> 
 OCI call OCIStmtPrepare <4>     sqlcrc = 53596 
     SELECT /*+ INDEX_ASC(T0 CUSTOMER##CUST_NUM) */ PROGRESS_RECID  
      FROM DOCTEST.CUSTOMER T0 WHERE ((CUST_NUM = :1))  order by CUST_NUM  
 OCI call OCIStmtExecute <4> 
 OCI call omru   <2> 
 OCI call OCIStmtExecute <2> 
 OCI call omru   <4> 
  
      Cursor <4> Rows processed 1 (last execution) 
 OCI call OCIHandleFree <0> 
  
      Cursor <2> Rows processed 1 (last execution) 
      Total Rows processed     1 (previous executions) 
 OCI call OCIHandleFree <0> 
  
      Cursor <3> Rows processed 20 (last execution) 
 OCI call OCIHandleFree <0>

<2>    The DataServer uses the cursor to compare schema information and fetch column values.

<3>   The DataServer executes the FIND FIRST CUSTOMER.

<4>   The DataServer retrieves CUSTOMER 2.

Query 5 

FOR EACH customer FIELDS (name cust-num postal-code):
    DISPLAY name cust-num postal-code.
END. 


OCI call OCIStmtPrepare <2>     sqlcrc = 41633 
     SELECT /*+ INDEX(T0 CUSTOMER##PROGRESS_RECID) */ * FROM DOCTEST.C 
     USTOMER T0 WHERE PROGRESS_RECID = :rid 
 OCI call OCIStmtExecute-DescribeOnly <2> 
 OCI call omru   <2> 
 OCI call OCIHandleAlloc <0> 
 OCI call OCIStmtPrepare <3>     sqlcrc = 60425 
     SELECT /*+ INDEX_ASC(T0 CUSTOMER##CUST_NUM)  */ PROGRESS_RECID un 
     ique_id_0,CUST_NUM,COUNTRY,NAME,ADDRESS,ADDRESS2,CITY,STATE,POSTA 
     L_CODE,CONTACT,PHONE,SALES_REP,CREDIT_LIMIT,BALANCE,TERMS,DISCOUN 
     T,COMMENTS,PROGRESS_RECID FROM DOCTEST.CUSTOMER T0 
 OCI call OCIStmtExecute <3> 
 OCI call OCIStmtFetch <3> 
 OCI call OCIStmtFetch <3> 
 OCI call OCIStmtFetch <3> 
 OCI call OCIStmtFetch <3> 
 OCI call OCIStmtFetch <3> 
 OCI call omru   <3> 
  
      Cursor <3> Rows processed 85 (last execution) 
      Number of array fetches  5 
      Number of rows fetched   85 
      Number of array rows     17 
      Number of array columns  18 
      Number of tables         1 
      Space for one row        472 
      Requested cache size     8192 
      Actual cache size used   8024 
 OCI call OCIHandleFree <0> 
  
      Cursor <2> Rows processed 0 (last execution) 
 OCI call OCIHandleFree <0>

<2>   The DataServer uses the cursor to compare schema information. It does not fetch any column values.

<3>   The single lookahead cursor selects columns directly. It ignores the field list because the FOR EACH loop defaults to a SHARE-LOCK. Also, since FOR EACH loops do not guarantee order of retrieval, the DataServer has not added an ORDER BY clause. The DataServer called OCIStmtFetch to fetch an array of rows. The DataServer used the default cache-size of 8192. Since 472 bytes are required for each row, it used 8024 bytes of cache to fetch up to 17 rows each call. Processing the 85 rows in the CUSTOMER table required a total of 5 array fetches.

Query 6 

FOR EACH customer NO-LOCK:
    DISPLAY name cust-num postal-code.
END. 


 OCI call OCIStmtPrepare <2>     sqlcrc = 41633 
     SELECT /*+ INDEX(T0 CUSTOMER##PROGRESS_RECID) */ * FROM DOCTEST.C 
     USTOMER T0 WHERE PROGRESS_RECID = :rid 
 OCI call OCIStmtExecute-DescribeOnly <2> 
 OCI call omru   <2> 
 OCI call OCIHandleAlloc <0> 
 OCI call OCIStmtPrepare <3>     sqlcrc = 60425 
     SELECT /*+ INDEX_ASC(T0 CUSTOMER##CUST_NUM)  */ PROGRESS_RECID un 
     ique_id_0,CUST_NUM,COUNTRY,NAME,ADDRESS,ADDRESS2,CITY,STATE,POSTA 
     L_CODE,CONTACT,PHONE,SALES_REP,CREDIT_LIMIT,BALANCE,TERMS,DISCOUN 
     T,COMMENTS,PROGRESS_RECID FROM DOCTEST.CUSTOMER T0 
 OCI call OCIStmtExecute <3> 
 OCI call OCIStmtFetch <3> 
 OCI call OCIStmtFetch <3> 
 OCI call OCIStmtFetch <3> 
 OCI call OCIStmtFetch <3> 
 OCI call OCIStmtFetch <3> 
 OCI call omru   <3> 
  
      Cursor <3> Rows processed 85 (last execution) 
      Number of array fetches  5 
      Number of rows fetched   85 
      Number of array rows     17 
      Number of array columns  18 
      Number of tables         1 
      Space for one row        472 
      Requested cache size     8192 
      Actual cache size used   8024 
 OCI call OCIHandleFree <0> 
  
      Cursor <2> Rows processed 0 (last execution) 
 OCI call OCIHandleFree <0>

<2>   The DataServer uses the cursor to compare schema information. It does not fetch any column values.

<3>   The single lookahead cursor selects columns directly. It selects all columns because the query does not contain a field list.

Query 7 

FOR EACH customer FIELDS (name cust-num postal-code) NO-LOCK:
    DISPLAY name cust-num postal-code.
END. 


 OCI call OCIStmtPrepare <2>     sqlcrc = 41633 
     SELECT /*+ INDEX(T0 CUSTOMER##PROGRESS_RECID) */ * FROM DOCTEST.C 
     USTOMER T0 WHERE PROGRESS_RECID = :rid 
 OCI call OCIStmtExecute-DescribeOnly <2> 
 OCI call omru   <2> 
 OCI call OCIHandleAlloc <0> 
 OCI call OCIStmtPrepare <3>     sqlcrc = 40887 
     SELECT /*+ INDEX_ASC(T0 CUSTOMER##CUST_NUM)  */ PROGRESS_RECID un 
     ique_id_0,CUST_NUM,NAME,POSTAL_CODE FROM DOCTEST.CUSTOMER T0 
 OCI call OCIStmtExecute <3> 
 OCI call omru   <3> 
  
      Cursor <3> Rows processed 85 (last execution) 
      Number of array fetches  1 
      Number of rows fetched   85 
      Number of array rows     105 
      Number of array columns  4 
      Number of tables         1 
      Space for one row        78 
      Requested cache size     8192 
      Actual cache size used   8190 
 OCI call OCIHandleFree <0> 
  
      Cursor <2> Rows processed 0 (last execution) 
 OCI call OCIHandleFree <0>

<2>   The DataServer uses the cursor to compare schema information. It does not fetch any column values.

<3>   The cursor selects only the fields in the field-list. The default cache-size of 8192 is sufficient to hold 106 rows. A single fetch retrieves the entire CUSTOMER table.

Query 8 

FOR EACH customer FIELDS (name cust-num postal-code) NO-LOCK
    QUERY-TUNING (NO-LOOKAHEAD):
    DISPLAY name cust-num postal-code.
END. 


 OCI call OCIStmtPrepare <2>     sqlcrc = 41633 
     SELECT /*+ INDEX(T0 CUSTOMER##PROGRESS_RECID) */ * FROM  
      DOCTEST.CUSTOMER T0 WHERE PROGRESS_RECID = :rid 
 OCI call OCIStmtExecute-DescribeOnly <2> 
 OCI call omru   <2> 
 OCI call OCIHandleAlloc <0> 
 OCI call OCIStmtPrepare <3>     sqlcrc = 57261 
     SELECT /*+ INDEX_ASC(T0 CUSTOMER##CUST_NUM)  */ PROGRESS_RECID  
      unique_id_0 FROM DOCTEST.CUSTOMER T0 
 OCI call OCIStmtExecute <3> 
 OCI call omru   <2> 
 OCI call OCIStmtExecute <2> 
 OCI call omru   <2> 
      < repeat for each row >  
 OCI call OCIStmtExecute <2> 
 OCI call omru   <3> 
  
      Cursor <3> Rows processed 85 (last execution) 
      Number of array fetches  1 
      Number of rows fetched   85 
      Number of array rows     256 
      Number of array columns  1 
      Number of tables         1 
      Space for one row        4 
      Requested cache size     1024 
      Actual cache size used   1024 
 OCI call OCIHandleFree <0> 
  
      Cursor <2> Rows processed 1 (last execution) 
      Total Rows processed     84 (previous executions) 
 OCI call OCIHandleFree <0>

<2>   The DataServer uses the cursor to compare schema information and fetch column values.

<3>   This is a standard cursor. The default cache size is 1024. Since the DataServer fetches only the PROGRESS_RECID column, it requires only 4 bytes for each row. A single fetch retrieves all 85 PROGRESS_RECID values in the CUSTOMER table.

Query 9 

FOR EACH customer FIELDS (name cust-num postal-code) NO-LOCK
    QUERY-TUNING (NO-LOOKAHEAD):
    DISPLAY name cust-num postal-code.
END. 


 OCI call OCIStmtPrepare <2>     sqlcrc = 41633 
     SELECT /*+ INDEX(T0 CUSTOMER##PROGRESS_RECID) */ * FROM DOCTEST.C 
     USTOMER T0 WHERE PROGRESS_RECID = :rid 
 OCI call OCIStmtExecute-DescribeOnly <2> 
 OCI call omru   <2> 
 OCI call OCIHandleAlloc <0> 
 OCI call OCIStmtPrepare <3>     sqlcrc = 57261 
     SELECT /*+ INDEX_ASC(T0 CUSTOMER##CUST_NUM)  */ PROGRESS_RECID un 
     ique_id_0 FROM DOCTEST.CUSTOMER T0 
 OCI call OCIStmtExecute <3> 
 OCI call omru   <2> 
 OCI call OCIStmtExecute <2> 
 OCI call omru   <2> 
       < repeat for each row > 
 OCI call OCIStmtExecute <2> 
 OCI call omru   <3> 
  
      Cursor <3> Rows processed 85 (last execution) 
      Number of array fetches  1 
      Number of rows fetched   85 
      Number of array rows     256 
      Number of array columns  1 
      Number of tables         1 
      Space for one row        4 
      Requested cache size     1024 
      Actual cache size used   1024 
 OCI call OCIHandleFree <0> 
  
      Cursor <2> Rows processed 1 (last execution) 
      Total Rows processed     84 (previous executions)

<2>   The DataServer uses the cursor to compare schema information and fetch column values.

<3>   This is a standard cursor. Note that the advantage of using a field list is lost by not using a lookahead cursor. The DataServer uses the schema comparison cursor to retrieve column values by the PROGRESS_RECID column. Only those fields mentioned in the field list are available to the client.

Query 10 

FOR EACH customer FIELDS (name cust-num postal-code) NO-LOCK,
    EACH order FIELDS (order-date sales-rep) OF customer NO-LOCK:
    DISPLAY customer.name customer.cust-num customer.postal-code
        order.order-date order.sales-rep.
END. 


 OCI call OCIStmtPrepare <2>     sqlcrc = 41633 
     SELECT /*+ INDEX(T0 CUSTOMER##PROGRESS_RECID) */ * FROM DOCTEST.C 
     USTOMER T0 WHERE PROGRESS_RECID = :rid 
 OCI call OCIStmtExecute-DescribeOnly <2> 
 OCI call omru   <2> 
 OCI call OCIHandleAlloc <0> 
 OCI call OCIStmtPrepare <3>     sqlcrc = 60664 
     SELECT /*+ INDEX(T0 ORDER_##PROGRESS_RECID) */ * FROM DOCTEST.ORD 
     ER_ T0 WHERE PROGRESS_RECID = :rid 
 OCI call OCIStmtExecute-DescribeOnly <3> 
 OCI call omru   <3> 
 OCI call OCIHandleAlloc <0> 
 OCI call OCIStmtPrepare <4>     sqlcrc = 51753 
     SELECT T0.PROGRESS_RECID unique_id_0,T0.CUST_NUM,T0.NAME,T0.POSTA 
     L_CODE,T1.PROGRESS_RECID unique_id_1,T1.ORDER_DATE,T1.SALES_REP F 
     ROM DOCTEST.CUSTOMER T0,DOCTEST.ORDER_ T1 WHERE (T1.CUST_NUM = T0 
     .CUST_NUM) 
 OCI call OCIStmtExecute <4> 
 OCI call OCIStmtFetch <4> 
 OCI call OCIStmtFetch <4> 
 OCI call omru   <4> 
  
      Cursor <4> Rows processed 207 (last execution) 
      Number of array fetches  3 
      Number of rows fetched   207 
      Number of array rows     87 
      Number of array columns  7 
      Number of tables         2 
      Space for one row        94 
      Requested cache size     8192 
      Actual cache size used   8178 
 OCI call OCIHandleFree <0> 
  
      Cursor <3> Rows processed 0 (last execution) 
 OCI call OCIHandleFree <0> 
  
      Cursor <2> Rows processed 0 (last execution) 
 OCI call OCIHandleFree <0>

<2>   The DataServer uses the cursor to compare schema information. It does not fetch any column values.

<3>   The DataServer uses the cursor to compare schema information. It does not fetch any column values.

<4>   The cursor is used to perform the join by the SQLDB. Since the query specifies NO-LOCK, this cursor selects the fields in the field list in addition to those that the client requires (T0.PROGRESS_RECID, T1.PROGRESS_RECID, T1.CUST_NUM). With the default cache size of 8192, processing the entire join requires 3 array fetches.

Query 11 

FOR EACH customer FIELDS (name cust-num postal-code) NO-LOCK,
    EACH order FIELDS (order-date sales-rep) OF customer SHARE-LOCK:
    DISPLAY customer.name customer.cust-num customer.postal-code
        order.order-date order.sales-rep.
END. 


 OCI call OCIStmtPrepare <2>     sqlcrc = 41633 
     SELECT /*+ INDEX(T0 CUSTOMER##PROGRESS_RECID) */ * FROM  
      DOCTEST.CUSTOMER T0 WHERE PROGRESS_RECID = :rid 
 OCI call OCIStmtExecute-DescribeOnly <2> 
 OCI call omru   <2> 
 OCI call OCIHandleAlloc <0> 
 OCI call OCIStmtPrepare <3>     sqlcrc = 60664 
     SELECT /*+ INDEX(T0 ORDER_##PROGRESS_RECID) */ * FROM  
      DOCTEST.ORDER_ T0 WHERE PROGRESS_RECID = :rid 
 OCI call OCIStmtExecute-DescribeOnly <3> 
 OCI call omru   <3> 
 OCI call OCIHandleAlloc <0> 
 OCI call OCIStmtPrepare <4>     sqlcrc = 51753 
     SELECT T0.PROGRESS_RECID unique_id_0,T0.CUST_NUM,T0.NAME, 
      T0.POSTAL_CODE,T1.PROGRESS_RECID unique_id_1,T1.ORDER_DATE, 
      T1.SALES_REP FROM DOCTEST.CUSTOMER T0,DOCTEST.ORDER_ T1 WHERE  
      (T1.CUST_NUM = T0.CUST_NUM) 
 OCI call OCIStmtExecute <4> 
 OCI call OCIStmtFetch <4> 
 OCI call OCIStmtFetch <4> 
 OCI call OCIHandleAlloc <0> 
 OCI call OCIStmtPrepare <5>     sqlcrc = 18715 
     SELECT /*+ INDEX(T0 ORDER_##PROGRESS_RECID) */ ORDER_NUM,CUST_NUM 
     ,TO_CHAR(ORDER_DATE,'YYYYMMDDHH24MISS'),TO_CHAR(SHIP_DATE,'YYYYMM 
     DDHH24MISS'),TO_CHAR(PROMISE_DATE,'YYYYMMDDHH24MISS'),CARRIER,INS 
     TRUCTIONS,PO,TERMS,SALES_REP,PROGRESS_RECID  FROM DOCTEST.ORDER_  
     T0 WHERE PROGRESS_RECID = :rid 
 OCI call omru   <5> 
 OCI call OCIStmtExecute <5> 
	< repeat for each row >  
 OCI call OCIStmtExecute <5> 
 OCI call omru   <4>

<2>   The DataServer uses the cursor to compare schema information. It does not fetch any column values.

<3>   The DataServer uses the cursor to compare schema information. It does not fetch any column values. Note that because the ORDER_ table contains a date, the DataServer does not reuse this cursor to fetch column values.

<4>   The cursor is used to perform the join by the SQLDB. The join still requires a lookahead cursor.

<5>   Since the query requests the ORDER_ row with a SHARE-LOCK, the DataServer must refetch each ORDER_ row to get all the columns. If the ORDER_ table did not have a record identifier (PROGRESS_RECID in this case), this query would fail. If you must retrieve the ORDER_ row with a SHARE-LOCK, removing the field list eliminates the need to refetch the row.

Query 12 

FOR EACH customer FIELDS (name cust-num postal-code) NO-LOCK,
    EACH order FIELDS (order-date sales-rep) OF customer NO-LOCK
    QUERY-TUNING (NO-LOOKAHEAD):
    DISPLAY customer.name customer.cust-num customer.postal-code
        order.order-date order.sales-rep.
END. 

OCI call OCIStmtPrepare <2>     sqlcrc = 41633 
    SELECT /*+ INDEX(T0 CUSTOMER##PROGRESS_RECID) */ * FROM  
      DOCTEST.CUSTOMER T0 WHERE PROGRESS_RECID = :rid 
OCI call OCIStmtExecute-DescribeOnly <2> 
OCI call omru   <2> 
OCI call OCIStmtPrepare <3>     sqlcrc = 60664 
    SELECT /*+ INDEX(T0 ORDER_##PROGRESS_RECID) */ * FROM  
      DOCTEST.ORDER_ T0 WHERE PROGRESS_RECID = :rid 
OCI call OCIStmtExecute-DescribeOnly <3> 
OCI call omru   <3> 
OCI call OCIStmtPrepare <4>     sqlcrc = 48763 
    SELECT T0.PROGRESS_RECID unique_id_0,T1.PROGRESS_RECID  
      unique_id_1 FROM DOCTEST.CUSTOMER T0,DOCTEST.ORDER_ T1  
      WHERE (T1.CUST_NUM = T0.CUST_NUM) 
OCI call OCIStmtExecute <4> 
OCI call omru   <2> 
OCI call OCIStmtExecute <2> 
OCI call OCIStmtPrepare <5>     sqlcrc = 18715 
    SELECT /*+ INDEX(T0 ORDER_##PROGRESS_RECID) */ ORDER_NUM,CUST_NUM 
    ,TO_CHAR(ORDER_DATE,'YYYYMMDDHH24MISS'),TO_CHAR(SHIP_DATE,'YYYYMM 
    DDHH24MISS'),TO_CHAR(PROMISE_DATE,'YYYYMMDDHH24MISS'),CARRIER,INS 
    TRUCTIONS,PO,TERMS,SALES_REP,PROGRESS_RECID  FROM DOCTEST.ORDER_  
    T0 WHERE PROGRESS_RECID = :rid 
OCI call omru   <5> 
OCI call OCIStmtExecute <5> 
OCI call omru   <2> 
OCI call OCIStmtExecute <2> 
      < repeat, alternating handles 5 and 2 > 
     Cursor <4> Rows processed 207 (last execution) 
     Number of array fetches  2 
     Number of rows fetched   207 
     Number of array rows     128 
     Number of array columns  2 
     Number of tables         2 
     Space for one row        8 
     Requested cache size     1024 
     Actual cache size used   1024 
OCI call OCIHandleFree <0> 
     Cursor <5> Rows processed 1 (last execution) 
     Total Rows processed     206 (previous executions) 
OCI call OCIHandleFree <0> 
     Cursor <2> Rows processed 1 (last execution) 
     Total Rows processed     203 (previous executions) 
OCI call OCIHandleFree <0> 
     Cursor <3> Rows processed 0 (last execution)

<2>   The DataServer uses the cursor to compare schema information and fetch CUSTOMER rows.

<3>   The DataServer uses the cursor to compare schema information. It does not fetch any column values. Note that because the ORDER_ table contains a date, the DataServer does not reuse this cursor to fetch column values.

<4>   The cursor is used to perform the join by the SQLDB. It uses a standard cursor for the join. Each row of the join requires 8 bytes of the cache because the join cursor fetches only the unique integer record identifiers.

<5>   The DataServer uses this cursor to fetch ORDER_ rows by the PROGRESS_RECID column. It cannot use the schema comparison cursor (<39>) because the DataServer must perform a TO_CHAR conversion on the date columns.

Query 13 

FOR EACH customer FIELDS (name cust-num postal-code) NO-LOCK,
    EACH order FIELDS (order-date sales-rep) OF customer NO-LOCK
    QUERY-TUNING (NO-JOIN-BY-SQLDB):
    DISPLAY customer.name customer.cust-num customer.postal-code
        order.order-date order.sales-rep.
END. 


OCI call OCIStmtPrepare <2>     sqlcrc = 41633 
    SELECT /*+ INDEX(T0 CUSTOMER##PROGRESS_RECID) */ * FROM DOCTEST.C 
    USTOMER T0 WHERE PROGRESS_RECID = :rid 
OCI call OCIStmtExecute-DescribeOnly <2> 
OCI call omru   <2> 
OCI call OCIStmtPrepare <3>     sqlcrc = 60664 
    SELECT /*+ INDEX(T0 ORDER_##PROGRESS_RECID) */ * FROM DOCTEST.ORD 
    ER_ T0 WHERE PROGRESS_RECID = :rid 
OCI call OCIStmtExecute-DescribeOnly <3> 
OCI call omru   <3> 
OCI call OCIStmtPrepare <4>     sqlcrc = 40887 
    SELECT /*+ INDEX_ASC(T0 CUSTOMER##CUST_NUM)  */ PROGRESS_RECID un 
    ique_id_0,CUST_NUM,NAME,POSTAL_CODE FROM DOCTEST.CUSTOMER T0 
OCI call OCIStmtExecute <4> 
OCI call OCIStmtPrepare <5>     sqlcrc = 17552 
    SELECT /*+ INDEX_ASC(T0 ORDER_##CUST_ORDER)  */ PROGRESS_RECID un 
    ique_id_0,CUST_NUM,ORDER_DATE,SALES_REP FROM DOCTEST.ORDER_ T0 WH 
    ERE (CUST_NUM = :1) 
OCI call OCIStmtExecute <5> 
	< repeat for each row > 
     Cursor <4> Rows processed 85 (last execution) 
     Number of array fetches  1 
     Number of rows fetched   85 
     Number of array rows     105 
     Number of array columns  4 
     Number of tables         1 
     Space for one row        78 
     Requested cache size     8192 
     Actual cache size used   8190 
OCI call OCIHandleFree <0> 
     Cursor <5> Rows processed 0 (last execution) 
     Total Rows processed     207 (previous executions) 
     Number of array fetches  0 
     Number of rows fetched   0 
     Number of array rows     141 
     Number of array columns  4 
     Number of tables         1 
     Space for one row        58 
     Requested cache size     8192 
     Actual cache size used   8178

<2>   The DataServer uses the cursor to compare schema information and fetch CUSTOMER rows.

<3>   The DataServer uses the cursor to compare schema information. It does not fetch any column values. Note that because the ORDER_ table contains a date, the DataServer does not reuse this cursor to fetch column values.

<4>   The DataServer uses a lookahead cursor to select fields in the field list in addition to those required by the client.

<5>   The lookahead cursor selects fields from the ORDER_ table that correspond to a particular CUSTOMER row (WHERE CUST_NUM = :1).

Copyright © 2005 Progress Software Corporation
 www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095